#!/usr/bin/env python3
#-*- coding:utf-8 -*-
import pymysql
import threading
from taosrest import connect, TaosRestConnection, TaosRestCursor
import time

con_mysql = pymysql.connect(host='127.0.0.1',
                        database='location', 
                        user='root', 
                        password='password',
                        charset='utf8')

con_taosrest: TaosRestConnection = connect(url="http://127.0.0.1:6041",
                        user="root",
                        password="password",
                        timeout=300)

"""
DROP TABLE IF EXISTS location.location;
CREATE STABLE location.location ( \
    locationtime TIMESTAMP, \
    lat DOUBLE, \
    lng DOUBLE, \
    speed DOUBLE) \
    TAGS (loginname NCHAR(50), \
      username NCHAR(50), \
      deviceimei NCHAR(50), \
      devicemodel NCHAR(100), \
      devicetype BOOL);
"""
start_time = time.time()
cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
cursor_mysql.execute('SELECT Id FROM location.location ORDER BY Id DESC LIMIT 1;') 
countlines = cursor_mysql.fetchall()
cursor_mysql.close()
count = countlines[0]['Id']

i = 0
j = 50000
taoscnt = 0
for i in range(0, count, 50000):
    cursor_mysql = con_mysql.cursor()
    cursor_mysql.execute('SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname , \
        LoginName, \
        MAX(UserName) AS UserName, \
        DeviceIMEI, \
        IFNULL(MAX(DeviceModel),\'\') AS DeviceModel, \
        IFNULL(MAX(DeviceType),\'\') AS DeviceType\
        FROM location.location \
        WHERE Id > %d \
        AND Id <= %d \
        GROUP BY LoginName,DeviceIMEI;' % (i,j))
    tbnamecur = cursor_mysql.fetchall()
    cursor_mysql.close()

    for tbname,loginname,username,deviceimei,devicemodel,devicetype in tbnamecur:
        cursor_mysql = con_mysql.cursor(cursor=pymysql.cursors.DictCursor)
        cursor_mysql.execute(f'SELECT CONCAT(\'t\',LoginName,\'_\',DeviceIMEI) AS tbname, \
          LocationTime, \
          Lat, \
          Lng, \
          Speed \
        FROM location.location \
        WHERE LoginName = \"%s\" \
          AND Id > %d \
          AND Id <= %d;' % (loginname,i,j))
        submission = cursor_mysql.fetchall()
        cursor_mysql.close()
        sql="INSERT INTO location.`%s` USING location.location TAGS (\'%s\',\'%s\',\'%s\',\'%s\',%s) VALUES " % (tbname,loginname,username,deviceimei,devicemodel,devicetype)
        if submission:
            for row in submission:
                sql += '(\'{}\',{},{},{}) '.format(row["LocationTime"],row["Lat"],row["Lng"],row["Speed"])
            sql += ";"
            #print(sql)
            cursor_taosrest: TaosRestCursor = con_taosrest.cursor()
            cursor_taosrest.execute(sql)
            taoscnt += cursor_taosrest.rowcount
            cursor_taosrest.close()
            stime = time.time() - start_time
            avgrow = int(taoscnt / stime)
            print("数据已迁移%d行,耗时%d秒,平均行%d/秒" % (taoscnt,stime,avgrow))
    else:
        i += 50000
        j += 50000
        continue
i += 50000
j += 50000

end_time = time.time()
deltatime = end_time - start_time
totalhour = int(deltatime / 3600)
totalminute = int((deltatime - totalhour * 3600) / 60)
totalsecond = int(deltatime - totalhour * 3600 - totalminute * 60)
print("数据全部迁移完毕,总计耗时:%d小时%d分%d秒!" %(totalhour, totalminute, totalsecond))

con_mysql.close()
con_taosrest.close()